// 导入模块
import mysql from 'mysql2/promise';
import dataBase from '../config/index.js';
import { comparePassword } from '../utils/bcrypt/index.js';

// 创建连接池
const pool = mysql.createPool({
  host: dataBase.HOST,
  user: dataBase.USERNAME,
  password: dataBase.PASSWORD,
  database: dataBase.DATABASE,
  port: Number(dataBase.PORT),
});

// mysql的连接
const allServices = {
  // 自己定义一个函数
  async query(sql: any, values: any) {
    let conn;
    try {
      conn = await pool.getConnection(); // 等待连接池连接
      const [rows, fields] = await conn.query(sql, values); // 执行sql语句
      return rows;
    } catch (error) {
      console.error('Database query error:', error);
      throw error; // 重新抛出错误，让调用者处理
    } finally {
      if (conn) conn.release(); // 确保连接总是被释放
    }
  },
};

// 登录
const userLogin = async (username: string, password: string) => {
  // 只查询用户表中的 id 和 username
  const sql = `
    SELECT id, username, password
    FROM users
    WHERE username = ?
  `;
  const users = await allServices.query(sql, [username]);

  if (users.length === 0) {
    throw new Error('用户不存在');
  }

  const user = users[0];
  // 验证密码
  const isPasswordValid = await comparePassword(password, user.password);

  if (!isPasswordValid) {
    throw new Error('密码错误');
  }

  // 返回用户信息（不包含密码）
  const { password: _, ...userInfo } = user;
  return userInfo;
};

// 注册
const userRegister = async (
  username: string,
  password: string,
  nickname: string
) => {
  // 先检查用户名是否已存在
  const checkUsernameSql = `
    SELECT id
    FROM users
    WHERE username = ?
  `;
  const existingUsername = await allServices.query(checkUsernameSql, [
    username,
  ]);

  if (existingUsername.length > 0) {
    throw new Error('用户名已被注册');
  }

  // 检查昵称是否已存在
  const checkNicknameSql = `
    SELECT id
    FROM users
    WHERE nickname = ?
  `;
  const existingNickname = await allServices.query(checkNicknameSql, [
    nickname,
  ]);

  if (existingNickname.length > 0) {
    throw new Error('该昵称已被使用');
  }

  // 插入新用户
  const insertSql = `
    INSERT INTO users (username, password, nickname)
    VALUES (?, ?, ?)
  `;
  const result = await allServices.query(insertSql, [
    username,
    password,
    nickname,
  ]);

  // 返回新用户信息（不包含密码）
  const newUserId = result.insertId;
  const newUser = {
    id: newUserId,
    username,
    nickname,
  };

  return newUser;
};

// 获取用户信息
const getUserInfo = async (id: number) => {
  const sql = `
    SELECT 
      u.username,
      u.nickname
    FROM users u
    WHERE u.id = ?
  `;
  return await allServices.query(sql, [id]);
};

// 更新用户信息
const updateUserInfo = async (id: number, name: string, username: string) => {
  const sql = `
    UPDATE users 
    SET username = ?, nickname = ?
    WHERE id = ?
  `;
  return await allServices.query(sql, [username, name, id]);
};

// 获取项目列表
const getProjectList = async (id: number) => {
  const sql = `
    SELECT 
      p.id AS projectId,
      p.name AS projectName,
      p.description AS projectDesc,
      p.create_time AS createTime,
      u.nickname AS creator
    FROM 
      projects p
    LEFT JOIN 
      users u ON p.creator_id = u.id
    WHERE 
      p.id IN (
        SELECT pm.project_id 
        FROM project_members pm 
        WHERE pm.user_id = ?
      )
      OR p.creator_id = ?
    ORDER BY 
      p.create_time DESC
  `;
  return await allServices.query(sql, [id, id]);
};

// 定义在函数外部的类型
interface ModuleNode {
  id: number;
  projectId: number;
  parentId: number | null;
  name: string;
  children?: ModuleNode[];
  apis?: ApiNode[];
}

interface ApiNode {
  id: number;
  moduleId: number;
  method: string;
  name: string;
  path?: string;
  description?: string;
  requestParams?: string;
  response?: string;
  apiStatus?: string;
}

// 获取项目模块树
const getProjectModuleTree = async (projectId: number, userId: number) => {
  // 1. 首先验证用户是否为项目成员
  const memberCheckSql = `
    SELECT COUNT(*) as isMember 
    FROM project_members 
    WHERE project_id = ? AND user_id = ?
  `;
  const memberCheck = await allServices.query(memberCheckSql, [
    projectId,
    userId,
  ]);

  if (!memberCheck[0].isMember) {
    throw new Error('用户不是该项目的成员');
  }

  // 2. 使用递归CTE查询模块树和API
  const treeQuerySql = `
    WITH RECURSIVE moduleTree AS (
  SELECT id, project_id, parent_id, name
  FROM modules
  WHERE project_id = ? AND parent_id IS NULL
  
  UNION ALL
  
  SELECT m.id, m.project_id, m.parent_id, m.name
  FROM modules m
  INNER JOIN moduleTree mt ON m.parent_id = mt.id
)
SELECT 
  mt.id, mt.project_id, mt.parent_id, mt.name,
  CASE 
    WHEN NOT EXISTS (
      SELECT 1 FROM modules WHERE parent_id = mt.id
    ) THEN (
      SELECT JSON_ARRAYAGG(
        JSON_OBJECT(
          'id', a.id,
          'moduleId', a.module_id,
          'method', a.method,
          'name', a.name
        )
      )
      FROM apis a
      WHERE a.module_id = mt.id
    )
    ELSE NULL
  END as apis
FROM moduleTree mt
ORDER BY mt.parent_id, mt.id
  `;
  const flatResults = await allServices.query(treeQuerySql, [projectId]);

  // 3. 将扁平结果转换为树形结构
  const buildTree = (
    items: any[],
    parentId: number | null = null
  ): ModuleNode[] => {
    return items
      .filter((item) => item.parent_id === parentId)
      .map((item) => {
        const node: ModuleNode = {
          id: item.id,
          projectId: item.project_id,
          parentId: item.parent_id,
          name: item.name,
        };

        // 如果有子模块，递归构建子树
        const children = buildTree(items, item.id);
        if (children.length > 0) {
          node.children = children;
        }

        // 如果没有子模块但有API，直接添加API列表
        if (!children.length && item.apis) {
          node.apis = item.apis; // 直接使用已解析的数组，不再使用JSON.parse()
        }

        return node;
      });
  };

  const moduleTree = buildTree(flatResults);
  return moduleTree;
};

// 获取项目详情
const getProjectInfo = async (projectId: number) => {
  const sql = `
    SELECT 
      p.id,
      p.name,
      p.description,
      p.creator_id,
      p.create_time,
      u.nickname as creator_name
    FROM projects p
    LEFT JOIN users u ON p.creator_id = u.id
    WHERE p.id = ?
  `;
  return await allServices.query(sql, [projectId]);
};

// 获取 api 详情
const getApiDetail = async (id: number) => {
  const sql = `
    SELECT 
      apis.*,
      creator.nickname as creator_nickname,
      updater.nickname as updater_nickname
    FROM apis
    LEFT JOIN users creator ON apis.creator_id = creator.id
    LEFT JOIN users updater ON apis.updater_id = updater.id
    WHERE apis.id = ?
  `;
  return await allServices.query(sql, [id]);
};

// 更新 api 详情
const updateApiDetail = async (updaterid: number, apiDetail: any) => {
  // 获取当前时间戳
  const updatetime = Math.floor(Date.now() / 1000);

  // 将对象类型字段序列化为 JSON 字符串
  const requestHeaders = JSON.stringify(apiDetail.request_headers || {});
  const requestParams = JSON.stringify(apiDetail.request_params || {});
  const responseParams = JSON.stringify(apiDetail.response_params || {});

  // 构建 SQL 更新语句
  const updateSql = `
    UPDATE apis
    SET 
      name = ?,
      method = ?,
      path = ?,
      description = ?,
      request_headers = ?,
      request_params = ?,
      response_example = ?,
      response_params = ?,
      request_example = ?,
      updated_time = ?,
      updater_id = ?
    WHERE id = ?
  `;

  // 执行更新
  await allServices.query(updateSql, [
    apiDetail.name || '未命名接口',
    apiDetail.method,
    apiDetail.path,
    apiDetail.description,
    requestHeaders, // 使用序列化后的 JSON 字符串
    requestParams, // 使用序列化后的 JSON 字符串
    apiDetail.response_example,
    responseParams, // 使用序列化后的 JSON 字符串
    apiDetail.request_example,
    updatetime,
    updaterid,
    apiDetail.id,
  ]);

  // 返回更新后的记录
  return await getApiDetail(apiDetail.id);
};

// 创建api
const createApiDetail = async (creator_id: number, apiDetail: any) => {
  // 获取当前时间戳
  const currentTime = Math.floor(Date.now() / 1000);

  // 将对象类型字段序列化为 JSON 字符串
  const requestHeaders = JSON.stringify(apiDetail.request_headers || {});
  const requestParams = JSON.stringify(apiDetail.request_params || {});
  const responseParams = JSON.stringify(apiDetail.reponse_params || {});

  // 构建 SQL 插入语句
  const insertSql = `
    INSERT INTO apis (
      name, method, path, description, module_id,
      request_headers, request_params, response_example, response_params, request_example,
      creator_id, updater_id, created_time, updated_time
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `;

  // 执行插入
  const result = await allServices.query(insertSql, [
    apiDetail.name || '未命名接口',
    apiDetail.method,
    apiDetail.path,
    apiDetail.description,
    apiDetail.module_id,
    requestHeaders, // 使用序列化后的 JSON 字符串
    requestParams, // 使用序列化后的 JSON 字符串
    apiDetail.response_example,
    responseParams, // 使用序列化后的 JSON 字符串
    apiDetail.request_example,
    creator_id, // 创建者ID
    creator_id, // 初始更新者ID也是创建者
    currentTime, // 创建时间
    currentTime, // 更新时间
  ]);

  // 返回新创建的记录
  return await getApiDetail(result.insertId);
};

// 删除api并返回所属模块id
const deleteApi = async (id: number) => {
  // 先查询获取当前api的moduleid
  const selectSql = 'SELECT module_id FROM apis WHERE id = ?';
  const selectResult = await allServices.query(selectSql, [id]);

  if (selectResult && selectResult.length > 0) {
    const { module_id } = selectResult[0];

    // 执行删除操作
    const deleteSql = 'DELETE FROM apis WHERE id = ?';
    await allServices.query(deleteSql, [id]);

    // 返回被删除API的moduleid
    return { module_id };
  }

  return null;
};

// 获取角色权限列表
const getRoleList = async (projectId: number) => {
  const _sql = `
    SELECT id,name,type
    FROM project_roles 
    WHERE project_id = ?
  `;

  // 执行查询并返回结果
  return await allServices.query(_sql, [projectId]);
};

// 获取项目成员列表
const getProjecMembertList = async (
  filter,
  projectId,
  pageNum,
  pageSize,
  orderBy
) => {
  // 构建基础查询
  let sql = `
    SELECT 
      pm.id,
      pm.project_id,
      pm.user_id,
      pm.role_id,
      pm.join_time,
      u.username,
      u.nickname,
      pr.name as role_name,
      pr.type as role_type
    FROM project_members pm
    LEFT JOIN users u ON pm.user_id = u.id
    LEFT JOIN project_roles pr ON pm.role_id = pr.id
    WHERE pm.project_id = ?
  `;

  const queryParams = [projectId];

  // 添加过滤条件
  if (filter.username) {
    sql += ` AND u.username LIKE ?`;
    queryParams.push(`%${filter.username}%`);
  }

  if (filter.nickname) {
    sql += ` AND u.nickname LIKE ?`;
    queryParams.push(`%${filter.nickname}%`);
  }

  if (filter.role_id) {
    sql += ` AND pm.role_id = ?`;
    queryParams.push(filter.role_id);
  }

  if (filter.joinTimeStamp && filter.joinTimeStamp.length === 2) {
    sql += ` AND pm.join_time BETWEEN ? AND ?`;
    queryParams.push(filter.joinTimeStamp[0], filter.joinTimeStamp[1]);
  }

  // 添加排序
  if (orderBy && orderBy.length > 0) {
    const orderClauses = orderBy.map((item) => {
      // 处理字段名映射（joinTime -> join_time）
      let field = item.field;
      if (field === 'joinTime') field = 'join_time';

      return `${field} ${item.strategy}`;
    });
    sql += ` ORDER BY ${orderClauses.join(', ')}`;
  }

  // 添加分页
  const offset = (pageNum - 1) * pageSize;
  sql += ` LIMIT ? OFFSET ?`;
  queryParams.push(pageSize, offset);

  // 执行查询
  const members = await allServices.query(sql, queryParams);

  // 获取总数（用于分页）
  let countSql = `
    SELECT COUNT(*) as total
    FROM project_members pm
    LEFT JOIN users u ON pm.user_id = u.id
    LEFT JOIN project_roles pr ON pm.role_id = pr.id
    WHERE pm.project_id = ?
  `;

  const countParams = [projectId];

  // 添加相同的过滤条件到计数查询
  if (filter.username) {
    countSql += ` AND u.username LIKE ?`;
    countParams.push(`%${filter.username}%`);
  }

  if (filter.nickname) {
    countSql += ` AND u.nickname LIKE ?`;
    countParams.push(`%${filter.nickname}%`);
  }

  if (filter.role_id) {
    countSql += ` AND pm.role_id = ?`;
    countParams.push(filter.role_id);
  }

  if (filter.joinTimeStamp && filter.joinTimeStamp.length === 2) {
    countSql += ` AND pm.join_time BETWEEN ? AND ?`;
    countParams.push(filter.joinTimeStamp[0], filter.joinTimeStamp[1]);
  }

  const countResult = await allServices.query(countSql, countParams);
  const total = countResult[0].total;

  return {
    data: members,
    pagination: {
      total,
      totalPages: Math.ceil(total / pageSize),
    },
  };
};

// 获取项目成员详情
const getProjectMemberDetail = async (id: number, projectId: number) => {
  const sql = `
    SELECT 
      u.username,
      u.nickname,
      pm.role_id,
      pm.join_time,
      pr.name as role_name
    FROM project_members pm
    JOIN users u ON pm.user_id = u.id
    LEFT JOIN project_roles pr ON pm.role_id = pr.id AND pm.role_id IS NOT NULL
    WHERE pm.user_id = ? AND pm.project_id = ?
  `;
  return await allServices.query(sql, [id, projectId]);
};

// 删除项目成员
const deleteProjectMember = async (id: number, projectId: number) => {
  const sql = `
    DELETE FROM project_members 
    WHERE user_id = ? AND project_id = ?
  `;
  return await allServices.query(sql, [id, projectId]);
};

// 更新项目成员详情
const updateProjectMemberDetail = async (
  id: number,
  projectId: number,
  role_id: number
) => {
  const sql = `
    UPDATE project_members 
    SET role_id = ? 
    WHERE user_id = ? AND project_id = ?
  `;
  return await allServices.query(sql, [role_id, id, projectId]);
};

// 项目角色的增删改查
const getRole = async (projectId: number, roleId: number) => {
  const sql = `
    SELECT 
      name,
      description
    FROM project_roles
    WHERE project_id = ? AND id = ?
  `;
  return await allServices.query(sql, [projectId, roleId]);
};

const createRole = async (
  projectId: number,
  name: string,
  description: string
) => {
  const checkSql = `
    SELECT id FROM project_roles 
    WHERE project_id = ? AND name = ?
  `;
  const existingRole = await allServices.query(checkSql, [projectId, name]);
  if (existingRole.length > 0) {
    throw new Error('角色名称已存在');
  }

  const sql = `
    INSERT INTO project_roles (project_id, name, description)
    VALUES (?, ?, ?)
  `;
  return await allServices.query(sql, [projectId, name, description]);
};

const updateRole = async (
  projectId: number,
  name: string,
  description: string,
  roleId: number
) => {
  // 检查名称是否已被其他角色使用
  const checkSql = `
    SELECT id FROM project_roles 
    WHERE project_id = ? AND name = ? AND id != ?
  `;
  const existingRole = await allServices.query(checkSql, [
    projectId,
    name,
    roleId,
  ]);

  if (existingRole.length > 0) {
    throw new Error('角色名称已存在');
  }

  const sql = `
    UPDATE project_roles 
    SET name = ?, description = ? 
    WHERE project_id = ? AND id = ?
  `;
  return await allServices.query(sql, [name, description, projectId, roleId]);
};

const deleteRole = async (projectId: number, roleId: number) => {
  let connection;
  try {
    // 获取连接并开始事务
    connection = await pool.getConnection();
    await connection.beginTransaction();

    // 1. 删除项目角色
    const deleteRoleSql = `
      DELETE FROM project_roles
      WHERE project_id = ? AND id = ?
    `;
    await connection.query(deleteRoleSql, [projectId, roleId]);

    // 2. 将项目成员中的角色ID置为NULL
    const updateMemberSql = `
      UPDATE project_members
      SET role_id = NULL
      WHERE project_id = ? AND role_id = ?
    `;
    await connection.query(updateMemberSql, [projectId, roleId]);

    // 3. 删除项目成员权限中与该角色相关的所有权限
    const deletePermissionSql = `
      DELETE FROM project_role_permissions
      WHERE project_role_id = ?
    `;
    await connection.query(deletePermissionSql, [roleId]);

    // 提交事务
    await connection.commit();
    return { success: true, message: '角色删除成功' };
  } catch (error) {
    // 发生错误时回滚事务
    if (connection) {
      await connection.rollback();
    }
    throw error;
  } finally {
    // 确保连接被释放
    if (connection) {
      connection.release();
    }
  }
};

// 获取角色权限树
const getRolePermissionTree = async (
  projectId: number,
  projectRoleId: number
) => {
  // 1. 先获取该角色拥有的所有权限ID
  const rolePermissionsSql = `
    SELECT permission_id 
    FROM project_role_permissions 
    WHERE project_role_id = ?
  `;
  const rolePermissions = await allServices.query(rolePermissionsSql, [
    projectRoleId,
  ]);

  // 转换为Set便于快速查找
  const rolePermissionIds = new Set(
    rolePermissions.map((item) => item.permission_id)
  );

  // 2. 获取所有权限的详细信息
  const allPermissionsSql = `
    SELECT id, name, parent_id, code 
    FROM permissions 
    ORDER BY parent_id, id
  `;
  const allPermissions = await allServices.query(allPermissionsSql);

  // 3. 创建权限映射并初始化树结构
  const permissionMap = new Map();
  const tree = [];

  allPermissions.forEach((permission) => {
    const node = {
      id: permission.id,
      name: permission.name,
      code: permission.code,
      checked: rolePermissionIds.has(permission.id), // 标记该角色是否拥有此权限
      children: [], // 确保每个节点都有children属性
    };
    permissionMap.set(permission.id, node);

    // 如果是根节点，直接加入树
    if (!permission.parent_id || permission.parent_id === 0) {
      tree.push(node);
    }
  });

  // 4. 构建树形结构
  allPermissions.forEach((permission) => {
    const node = permissionMap.get(permission.id);
    if (permission.parent_id && permissionMap.has(permission.parent_id)) {
      permissionMap.get(permission.parent_id).children.push(node);
    }
  });

  // 5. 确保所有节点都有children属性（即使为空数组）
  permissionMap.forEach((node) => {
    if (!node.children) {
      node.children = [];
    }
  });

  return tree;
};

// 增量更新角色权限（mysql2/promise 版本）
const updateRolePermissions = async (
  projectRoleId: number,
  nextPermissionIds: number[]
) => {
  // 1) 查当前已有的权限
  const selectSql = `
    SELECT permission_id 
    FROM project_role_permissions
    WHERE project_role_id = ?
  `;
  const rows = (await allServices.query(selectSql, [projectRoleId])) as Array<{
    permission_id: number;
  }>;
  const currentPermissionIds = rows.map((r) => r.permission_id);

  // 2) 计算差集
  const toAdd = nextPermissionIds.filter(
    (id) => !currentPermissionIds.includes(id)
  );
  const toRemove = currentPermissionIds.filter(
    (id) => !nextPermissionIds.includes(id)
  );

  // 3) 删除消失的权限
  if (toRemove.length > 0) {
    const placeholders = toRemove.map(() => '?').join(',');
    const deleteSql = `
      DELETE FROM project_role_permissions
      WHERE project_role_id = ? AND permission_id IN (${placeholders})
    `;
    await allServices.query(deleteSql, [projectRoleId, ...toRemove]);
  }

  // 4) 批量新增新增的权限
  if (toAdd.length > 0) {
    const values = toAdd.map((id) => [projectRoleId, id]); // [[roleId, permId], ...]
    const insertSql = `
      INSERT INTO project_role_permissions (project_role_id, permission_id)
      VALUES ${values.map(() => '(?, ?)').join(', ')}
    `;
    await allServices.query(insertSql, values.flat());
  }

  return { toAdd, toRemove };
};

// 获取项目顶级模块列表
const getTopLevelModules = async (projectId: number) => {
  const sql = `
    SELECT id, project_id, parent_id, name
    FROM modules
    WHERE project_id = ? AND parent_id IS NULL
  `;
  const modules = await allServices.query(sql, [projectId]);

  // 为顶级模块添加唯一key和type
  return modules.map((item) => ({
    ...item,
    type: 'module',
    key: `module-${item.id}`, // 添加前缀确保唯一性
  }));
};

// 获取该模块下的子模块或API列表
// 在getModuleChildren函数中修改返回的数据格式
const getModuleChildren = async (projectId: number, id: number) => {
  // 先查询子模块
  const moduleSql = `
    SELECT id, project_id, parent_id, name, 'module' as type
    FROM modules
    WHERE project_id = ? AND parent_id = ?
  `;
  const modules = await allServices.query(moduleSql, [projectId, id]);

  if (modules.length > 0) {
    // 为模块添加唯一key
    return modules.map((item) => ({
      ...item,
      key: `module-${item.id}`, // 添加前缀确保唯一性
    }));
  }

  // 如果没有子模块，查询API列表
  const apiSql = `
    SELECT id, module_id, method, name, 'api' as type
    FROM apis
    WHERE module_id = ?
  `;
  const apis = await allServices.query(apiSql, [id]);

  // 为API添加唯一key
  return apis.map((item) => ({
    ...item,
    key: `api-${item.id}`, // 添加前缀确保唯一性
  }));
};

// 根据api的id查到找到该id最短路径
// const getShortestTreeById = async (
//   id: number,
//   project_id: number,
//   type: string
// ) => {
//   const sql = `
//     WITH RECURSIVE module_path AS (
//       -- 基础查询：找到API所在的模块
//       SELECT
//         m.id,
//         m.project_id,
//         m.parent_id,
//         m.name,
//         0 AS depth,
//         CAST(m.id AS CHAR(255)) AS path
//       FROM modules m
//       JOIN apis a ON m.id = a.module_id
//       WHERE a.id = ?

//       UNION ALL

//       -- 递归查询：向上查找父节点
//       SELECT
//         m.id,
//         m.project_id,
//         m.parent_id,
//         m.name,
//         mp.depth + 1,
//         CONCAT(m.id, ',', mp.path) AS path
//       FROM modules m
//       JOIN module_path mp ON m.id = mp.parent_id  -- 向上查找
//       WHERE m.id IS NOT NULL
//     )
//     SELECT
//       id,
//       project_id,
//       parent_id,
//       name,
//       path
//     FROM module_path
//     ORDER BY depth DESC;  -- 按深度排序，根节点在前
//   `;

//   return await allServices.query(sql, [id]);
// };
const getShortestTreeById = async (
  id: number,
  project: number,
  type: string
) => {
  // 根据类型构建不同的基础查询
  let baseQuery = '';
  if (type === 'api') {
    baseQuery = `
      SELECT
        m.id,
        m.project_id,
        m.parent_id,
        m.name,
        0 AS depth,
        CAST(m.id AS CHAR(255)) AS path
      FROM modules m
      JOIN apis a ON m.id = a.module_id
      WHERE a.id = ?
    `;
  } else if (type === 'module') {
    baseQuery = `
      SELECT
        m.id,
        m.project_id,
        m.parent_id,
        m.name,
        0 AS depth,
        CAST(m.id AS CHAR(255)) AS path
      FROM modules m
      WHERE m.id = ?
    `;
  } else {
    throw new Error('Invalid type. Must be "api" or "module"');
  }

  const sql = `
    WITH RECURSIVE module_path AS (
      -- 基础查询：根据类型查找起始节点
      ${baseQuery}

      UNION ALL

      -- 递归查询：向上查找父节点
      SELECT
        m.id,
        m.project_id,
        m.parent_id,
        m.name,
        mp.depth + 1,
        CONCAT(m.id, ',', mp.path) AS path
      FROM modules m
      JOIN module_path mp ON m.id = mp.parent_id  -- 向上查找
      WHERE m.id IS NOT NULL
    )
    SELECT
      id,
      project_id,
      parent_id,
      name,
      path
    FROM module_path
    ORDER BY depth DESC;  -- 按深度排序，根节点在前
  `;

  return await allServices.query(sql, [id]);
};

// 新增模块
const AddModuleApi = async (
  project_id: number,
  parent_id: number,
  name: string
) => {
  const sql = `
    INSERT INTO modules (project_id, parent_id, name)
    VALUES (?, ?, ?)
  `;
  return await allServices.query(sql, [project_id, parent_id, name]);
};

// 编辑模块
const editModuleApi = async (
  id: number,
  project_id: number,
  parent_id: number | null, // 明确parent_id可以为null
  name: string
) => {
  let sql = 'UPDATE modules SET name = ? WHERE id = ? AND project_id = ?';
  const params = [name, id, project_id];

  if (parent_id !== null && parent_id !== undefined) {
    sql += ' AND parent_id = ?';
    params.push(parent_id);
  }

  return await allServices.query(sql, params);
};

// 获取项目内权限
const getProjectPermissionsApi = async (id: number, projectId: number) => {
  // 第一步：根据 user_id 和 project_id 查询 role_id
  const getRoleSql = `
    SELECT role_id 
    FROM project_members 
    WHERE user_id = ? AND project_id = ?
  `;
  const roleResult = await allServices.query(getRoleSql, [id, projectId]);

  // 如果没有找到对应的角色，返回空数组
  if (!roleResult || roleResult.length === 0) {
    return [];
  }

  const roleId = roleResult[0].role_id;

  // 第二步：根据 role_id 查询所有 permission_id
  const getPermissionIdsSql = `
    SELECT permission_id 
    FROM project_role_permissions 
    WHERE project_role_id = ?
  `;
  const permissionIdsResult = await allServices.query(getPermissionIdsSql, [
    roleId,
  ]);

  // 如果没有找到权限ID，返回空数组
  if (!permissionIdsResult || permissionIdsResult.length === 0) {
    return [];
  }

  // 提取所有 permission_id
  const permissionIds = permissionIdsResult.map((item) => item.permission_id);

  // 第三步：根据 permission_id 查询所有权限详情
  const getPermissionsSql = `
    SELECT id, name, code 
    FROM permissions 
    WHERE id IN (${permissionIds.map(() => '?').join(',')})
  `;
  const permissionsResult = await allServices.query(
    getPermissionsSql,
    permissionIds
  );

  return permissionsResult || [];
};

// 获取所有用户列表
const getUserList = async () => {
  const sql = `SELECT id, username, nickname FROM users;`;
  return await allServices.query(sql, []);
};

// 添加项目成员
const addProjectMember = async (userId: number, projectId: number) => {
  // 先检查用户是否已经在项目中
  const checkSql = `SELECT id FROM project_members WHERE user_id = ? AND project_id = ?`;
  const existingMember = await allServices.query(checkSql, [userId, projectId]);

  if (existingMember.length > 0) {
    throw new Error('该用户已经是项目成员了');
  }

  // 如果不在项目中，则添加
  const joinTime = Math.floor(Date.now() / 1000); // 获取10位时间戳
  const sql = `INSERT INTO project_members (user_id, project_id, join_time) VALUES (?, ?, ?)`;
  return await allServices.query(sql, [userId, projectId, joinTime]);
};

// 删除模块
const deleteModuleApi = async (id: number, projectId: number) => {
  try {
    // 先获取要删除模块的父模块ID，用于后续返回
    const getParentSql = `
      SELECT parent_id FROM modules 
      WHERE id = ? AND project_id = ?
    `;
    const parentResult = await allServices.query(getParentSql, [id, projectId]);
    const parentId = parentResult.length > 0 ? parentResult[0].parent_id : null;

    // 1. 首先删除所有子模块的 API
    const deleteApisSql = `
      DELETE FROM apis 
      WHERE module_id IN (
        WITH RECURSIVE childmodules AS (
          -- 基础查询：获取当前模块
          SELECT id FROM modules 
          WHERE id = ? AND project_id = ?
          
          UNION ALL
          
          -- 递归查询：获取所有子模块
          SELECT m.id FROM modules m
          INNER JOIN childmodules cm ON m.parent_id = cm.id
        )
        SELECT id FROM childmodules
      )
    `;
    await allServices.query(deleteApisSql, [id, projectId]);

    // 2. 然后删除所有子模块（包括当前模块）
    const deleteModulesSql = `
      DELETE FROM modules 
      WHERE id IN (
        WITH RECURSIVE childmodules AS (
          -- 基础查询：获取当前模块
          SELECT id FROM modules 
          WHERE id = ? AND project_id = ?
          
          UNION ALL
          
          -- 递归查询：获取所有子模块
          SELECT m.id FROM modules m
          INNER JOIN childmodules cm ON m.parent_id = cm.id
        )
        SELECT id FROM childmodules
      )
    `;
    await allServices.query(deleteModulesSql, [id, projectId]);

    // 如果父模块存在，返回父模块ID；否则返回null
    return {
      success: true,
      parentId: parentId,
    };
  } catch (error) {
    console.error('删除模块失败:', error);
    throw error;
  }
};

// 创建项目
const addProjectApi = async (
  creator_id: number,
  name: string,
  description: string
) => {
  try {
    // 开始事务
    await allServices.query('START TRANSACTION');

    // 1. 在projects表中插入新项目
    const createProjectSql = `
      INSERT INTO projects (creator_id, name, description, create_time)
      VALUES (?, ?, ?, UNIX_TIMESTAMP())
    `;
    const projectResult = await allServices.query(createProjectSql, [
      creator_id,
      name,
      description,
    ]);
    const projectId = projectResult.insertId;

    // 2. 在projectroles表中插入超级管理员角色
    const createRoleSql = `
      INSERT INTO project_roles (project_id, name, type)
      VALUES (?, '超级管理员', 'admin')
    `;
    const roleResult = await allServices.query(createRoleSql, [projectId]);
    const roleId = roleResult.insertId;

    // 3. 在projectmembers表中添加创建者作为项目成员
    const createMemberSql = `
      INSERT INTO project_members (project_id, user_id, role_id, join_time)
      VALUES (?, ?, ?, UNIX_TIMESTAMP())
    `;
    await allServices.query(createMemberSql, [projectId, creator_id, roleId]);

    // 4. 在project_role_permissions中添加所有权限
    const addPermissionsSql = `
      INSERT INTO project_role_permissions (project_role_id, permission_id)
      SELECT ?, id FROM permissions
    `;
    await allServices.query(addPermissionsSql, [roleId]);

    // 提交事务
    await allServices.query('COMMIT');

    return {
      success: true,
      projectId: projectId,
    };
  } catch (error) {
    // 发生错误时回滚事务
    await allServices.query('ROLLBACK');
    console.error('创建项目失败:', error);
    throw error;
  }
};

// 更新项目
const updateProjectInfo = async (
  projectId: number,
  name: string,
  description: string
) => {
  const sql = `
    UPDATE projects 
    SET name = ?, description = ? 
    WHERE id = ?
  `;
  return await allServices.query(sql, [name, description, projectId]);
};

// 删除项目
const deleteProject = async (projectId: number) => {
  try {
    // 删除 APIs 表中与项目相关的数据
    await allServices.query(
      `DELETE FROM apis 
       WHERE module_id IN (
         SELECT id FROM modules WHERE project_id = ?
       )`,
      [projectId]
    );

    // 删除 modules 表中与项目相关的数据
    await allServices.query(
      `DELETE FROM modules 
       WHERE project_id = ?`,
      [projectId]
    );

    // 删除 project_role_permissions 表中与项目角色相关的数据
    await allServices.query(
      `DELETE FROM project_role_permissions 
       WHERE project_role_id IN (
         SELECT id FROM project_roles WHERE project_id = ?
       )`,
      [projectId]
    );

    // 删除 project_members 表中与项目相关的数据
    await allServices.query(
      `DELETE FROM project_members 
       WHERE project_id = ?`,
      [projectId]
    );

    // 删除 project_roles 表中与项目相关的数据
    await allServices.query(
      `DELETE FROM project_roles 
       WHERE project_id = ?`,
      [projectId]
    );

    // 最后删除项目本身
    await allServices.query(
      `DELETE FROM projects 
       WHERE id = ?`,
      [projectId]
    );

    return { success: true, message: '项目及其相关数据已成功删除' };
  } catch (error) {
    console.error('删除项目失败:', error);
    throw error;
  }
};

// 搜索api
const searchApis = async (projectId: number, searchKey: number) => {
  const sql = `
    SELECT 
      a.id,
      a.method,
      a.name,
      m.name as module_name
    FROM apis a
    JOIN modules m ON a.module_id = m.id
    WHERE m.project_id = ? AND a.name LIKE ?
    ORDER BY a.id
  `;

  // 将数字searchKey转为字符串，并添加%进行模糊匹配
  const searchPattern = `%${searchKey}%`;

  return await allServices.query(sql, [projectId, searchPattern]);
};

// 获取数据统计
const getDataStatistics = async (id: number) => {
  const sql = `
    SELECT 
      (SELECT COUNT(*) FROM modules WHERE project_id = ?) as moduleCount,
      (SELECT COUNT(*) FROM apis WHERE module_id IN (SELECT id FROM modules WHERE project_id = ?)) as apiCount,
      (SELECT COUNT(*) FROM project_members WHERE project_id = ?) as memberCount
  `;
  return await allServices.query(sql, [id, id, id]);
};

export default {
  userLogin,
  userRegister,
  getUserInfo,
  updateUserInfo,
  getProjectList,
  getProjectModuleTree,
  getApiDetail,
  updateApiDetail,
  createApiDetail,
  getProjectInfo,
  getRoleList,
  getProjecMembertList,
  getRole,
  createRole,
  updateRole,
  deleteRole,
  getRolePermissionTree,
  updateRolePermissions,
  getProjectMemberDetail,
  updateProjectMemberDetail,
  deleteProjectMember,
  getTopLevelModules,
  getModuleChildren,
  deleteApi,
  getShortestTreeById,
  AddModuleApi,
  editModuleApi,
  getProjectPermissionsApi,
  getUserList,
  addProjectMember,
  deleteModuleApi,
  addProjectApi,
  updateProjectInfo,
  deleteProject,
  searchApis,
  getDataStatistics,
};
